using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using T.FAS.Runtime.Metadata.Entity.DataModel;
using T.FAS.Runtime.DataAccess.SQLDom;
using T.FAS.Runtime.Base.Entity;
using T.FAS.Runtime.DataAccess.Common;
using System.Data;
using DataColumn = T.FAS.Runtime.Metadata.Entity.DataModel.DataColumn;
using System.Text.RegularExpressions;
using T.ServiceComponent.I18N.Service;

namespace T.FAS.Runtime.DataAccess.Engine
{
    /// <summary>
    /// DQ查询SQL Dom构造策略
    /// </summary>
    internal class QuerySQLBuildStrategy
    {
        /// <summary>
        /// 查询SQL DOM构造器
        /// </summary>
        /// <param name="metadata">DQ元数据对象</param>
        /// <param name="variableList">自定义变量字典</param>
        /// <param name="controlContext">控制上下文</param>
        /// <param name="isCanPaging">out参数:是否支持分页</param>
        /// <param name="isHaveFilter">out参数:是否包含过滤条件</param>
        /// <param name="pageSize">每页大小</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="advanceQuerySQL">高级查询条件</param>
        /// <param name="advancedSorts">高级查询排序</param>
        /// <param name="authSQLs">权限SQL</param>
        /// <param name="paramPrefix">暂时没用</param>
        /// <param name="isSubQuery">是否为子查询</param>
        /// <returns></returns>
        /// <exception cref="FasException"></exception>
        /// <exception cref="Exception"></exception>
        public static SqlStatement BuildTableSqlSchema(DataQuery metadata,
                                                        Dictionary<string, object> variableList,
                                                        DataQueryControlContext controlContext,
                                                        out bool isCanPaging,
                                                        out bool isHaveFilter,
                                                        out DataQuery virDq,
                                                        int pageSize = 0,
                                                        int pageIndex = 1,
                                                        AdvanceQuerySQL advanceQuerySQL = default,
                                                        List<AdvancedSort> advancedSorts = default,
                                                        string authSQLs = default,
                                                        string paramPrefix = default,
                                                        bool isSubQuery = false
                                                        )
        {
            if (metadata == null || metadata.DataQuerySources == null || metadata.DataQuerySources.Count <= 0)
                throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema);
            virDq = null;
            //如果高级查询条件中包含指定group列，则构造虚拟dq执行
            if (controlContext.AdvancedFilterExtSetting?.GroupColumns?.Count > 0)
            {
                virDq = VirtualModelService.GenerateVirtualDQ(metadata, controlContext.AdvancedFilterExtSetting.GroupColumns);
                //分组聚合不需要分页
                pageSize = 0;
                pageIndex = 1;
                metadata = virDq;
                //分组聚合不需要排序
                virDq.DataQuerySorts = new List<DataQuerySort>();
            }

            //如果自定义变量集合不为空，预处理所有DQ的属性
            if (variableList != null && variableList.Keys.Count > 0)
            {
                foreach (string key in variableList.Keys)
                {
                    var actKey = $"@{key}";
                    var variableValue = variableList[key]?.ToString();
                    var expressCols = metadata.DataQueryColumns.Where(c => c.ColumnType == ColumnType.Express).ToList();
                    var expressSorts = metadata?.DataQuerySorts.Where(s => s.SortItemType == 1).ToList();
                    //处理DQ自定义列
                    foreach (var col in expressCols)
                    {
                        col.ColumnExpression = Replace(col.ColumnExpression, actKey, variableValue);
                        col.ColumnExpressionJson = Replace(col.ColumnExpressionJson, actKey, variableValue);
                    }
                    //处理DQ自定义条件
                    if (metadata.DataQueryJoins != null && metadata.DataQueryJoins.Count > 0)
                    {
                        foreach (var join in metadata.DataQueryJoins)
                        {
                            join.JoinExpression = Replace(join.JoinExpression, actKey, variableValue);
                            join.JoinExpressionJson = Replace(join.JoinExpressionJson, actKey, variableValue);
                        }
                    }
                    //处理DQ自定义条件
                    if (metadata.DataQueryFilters != null && metadata.DataQueryFilters.Count > 0)
                    {
                        foreach (var filter in metadata.DataQueryFilters)
                        {
                            filter.FilterExpression = Replace(filter.FilterExpression, actKey, variableValue);
                            filter.FilterExpressionJson = Replace(filter.FilterExpressionJson, actKey, variableValue);
                        }
                    }
                    //处理DQ自定义排序
                    if (expressSorts != null && expressSorts.Count > 0)
                    {
                        foreach (var sort in expressSorts)
                        {
                            sort.SortExpression = Replace(sort.SortExpression, actKey, variableValue);
                            sort.SortExpressionJson = Replace(sort.SortExpressionJson, actKey, variableValue);
                        }
                    }
                    //特殊处理DQ高级查询使用自定义列做过滤时，里面同时使用自定义变量的场景
                    if (!string.IsNullOrEmpty(advanceQuerySQL?.SQL))
                    {
                        advanceQuerySQL.SQL = Replace(advanceQuerySQL.SQL, actKey, variableValue);
                    }
                }
            }
            isHaveFilter = false;
            #region Build Source
            var mainSource = metadata.DataQuerySources.FirstOrDefault();
            if (mainSource == null || mainSource.DataObject == null)
                throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema1 + metadata.ID);
            if (mainSource.DataObject.Database == null)
                throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema2 + metadata.ID);
            if (mainSource.DataObject.Database.DatabaseConfigs == null || mainSource.DataObject.Database.DatabaseConfigs.Count <= 0)
                throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema3 + metadata.ID);
            DatabaseType dbType = DBTypeConvert(mainSource.DataObject.Database.DbType);

            var dataSource = mainSource.DataObject.Database.DatabaseConfigs.FirstOrDefault(x => x.IsDefault == true);
            if (dataSource == null)
                throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema4 + mainSource.DataObject.Database.Name);

            var selectStatement = SQLStatementFactory.CreateSqlStatement(SqlStatementType.Select, dbType) as SelectSqlStatement;
            selectStatement.DatabaseConfigKey = dataSource.ConfigKey;
            selectStatement.MetaDataCode = metadata.Code ?? metadata.MetaDataCode;
            selectStatement.TableName = metadata.Code;
            selectStatement.PageCount = pageSize;
            selectStatement.PageIndex = pageIndex <= 0 ? 1 : pageIndex;
            #endregion

            #region Build Distinct
            if (metadata.IsDistinct)
            {
                selectStatement.Distinct.IsEnabled = true;
            }
            #endregion

            #region  Build From and Join
            if (metadata.DataQueryJoins != null && metadata.DataQueryJoins.Count > 0)
            {
                List<string> joinObj = new List<string>();
                var joins = metadata.DataQueryJoins.OrderBy(x => x.Sort).ToList();
                for (int i = 0; i < joins.Count; i++)
                {
                    var join = joins[i];
                    if (join.LeftQuerySource == null || join.LeftQuerySource.DataObject == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema5 + metadata.ID);
                    if (string.IsNullOrEmpty(join.LeftQuerySource.Alias))
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema6 + metadata.ID);
                    if (join.RightQuerySource == null || join.RightQuerySource.DataObject == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema7 + metadata.ID);
                    if (string.IsNullOrEmpty(join.RightQuerySource.Alias))
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema8 + metadata.ID);
                    if (string.IsNullOrEmpty(join.JoinExpression))
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema9 + metadata.ID);

                    if (i == 0) //增加第一个join前的对象
                    {
                        selectStatement.From.ChildCollection.Add(new SqlTable(GetTableName(join.LeftQuerySource.DataObject), join.LeftQuerySource.Alias));
                        joinObj.Add(join.LeftQuerySource.Alias);
                    }

                    //0:左连接 1:内连接 2:全连接
                    if (join.JoinType == 1)
                    {
                        var sIndex = join.JoinExpression.IndexOf('=');
                        if (sIndex < 0)
                            throw new Exception(string.Format(CommonStringClassEngine.QuerySQLBuildStrategy_CheckJoinConditionFail, join.JoinExpression));
                        var listF = new List<string>();
                        listF.Add(join.JoinExpression.Substring(0, sIndex));
                        listF.Add(join.JoinExpression.Substring(sIndex + 1));
                        string[] fileds = listF.ToArray();

                        JoinConditionItem joinItem = new JoinConditionItem();
                        joinItem.LeftField.Table = new SqlTable(GetTableName(join.LeftQuerySource.DataObject), join.LeftQuerySource.Alias);
                        joinItem.LeftField.FieldName = fileds[0];
                        joinItem.LeftField.IsNotNeedWrapsName = true;
                        joinItem.LeftField.IsUseFieldPrefix = false;

                        joinItem.RightField.Table = new SqlTable(GetTableName(join.RightQuerySource.DataObject), join.RightQuerySource.Alias);
                        joinItem.RightField.FieldName = fileds[1];
                        joinItem.RightField.IsNotNeedWrapsName = true;
                        joinItem.RightField.IsUseFieldPrefix = false;
                        //内连接
                        InnerJoinItem innerJoin = new InnerJoinItem();
                        innerJoin.InnerJoinTable = new SqlTable(GetTableName(join.RightQuerySource.DataObject), join.RightQuerySource.Alias);
                        joinObj.Add(join.RightQuerySource.Alias);

                        if (joinObj.Contains(join.LeftQuerySource.Alias) == false) //左侧条件表不存在，自动补齐join
                        {
                            LeftJoinItem tJoin = new LeftJoinItem();
                            tJoin.LeftJoinTable = new SqlTable(GetTableName(join.LeftQuerySource.DataObject), join.LeftQuerySource.Alias);
                            selectStatement.MainFromItem.ChildCollection.Add(tJoin);
                            joinObj.Add(join.LeftQuerySource.Alias);
                        }

                        innerJoin.ChildCollection.Add(joinItem);
                        selectStatement.MainFromItem.ChildCollection.Add(innerJoin);
                    }
                    else if (join.JoinType == 0)
                    {
                        var sIndex = join.JoinExpression.IndexOf('=');
                        if (sIndex < 0)
                            throw new Exception(string.Format(CommonStringClassEngine.QuerySQLBuildStrategy_CheckJoinConditionFail, join.JoinExpression));
                        var listF = new List<string>();
                        listF.Add(join.JoinExpression.Substring(0, sIndex));
                        listF.Add(join.JoinExpression.Substring(sIndex + 1));
                        string[] fileds = listF.ToArray();
                        //string[] fileds = join.JoinExpression.Split('=');
                        //if (fileds.Length != 2)
                        //    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, "DQ条件设置不正确。正确格式：Field1=Field2。实际：" + join.JoinExpression);

                        JoinConditionItem joinItem = new JoinConditionItem();
                        joinItem.LeftField.Table = new SqlTable(GetTableName(join.LeftQuerySource.DataObject), join.LeftQuerySource.Alias);
                        joinItem.LeftField.FieldName = fileds[0];
                        joinItem.LeftField.IsNotNeedWrapsName = true;
                        joinItem.LeftField.IsUseFieldPrefix = false;

                        joinItem.RightField.Table = new SqlTable(GetTableName(join.RightQuerySource.DataObject), join.RightQuerySource.Alias);
                        joinItem.RightField.FieldName = fileds[1];
                        joinItem.RightField.IsNotNeedWrapsName = true;
                        joinItem.RightField.IsUseFieldPrefix = false;
                        LeftJoinItem leftJoin = new LeftJoinItem();
                        leftJoin.LeftJoinTable = new SqlTable(GetTableName(join.RightQuerySource.DataObject), join.RightQuerySource.Alias);
                        joinObj.Add(join.RightQuerySource.Alias);

                        if (joinObj.Contains(join.LeftQuerySource.Alias) == false)//左侧条件表不存在，自动补齐join
                        {
                            LeftJoinItem tJoin = new LeftJoinItem();
                            tJoin.LeftJoinTable = new SqlTable(GetTableName(join.LeftQuerySource.DataObject), join.LeftQuerySource.Alias);
                            selectStatement.MainFromItem.ChildCollection.Add(tJoin);
                            joinObj.Add(join.LeftQuerySource.Alias);
                        }

                        leftJoin.ChildCollection.Add(joinItem);
                        selectStatement.MainFromItem.ChildCollection.Add(leftJoin);
                    }
                    else if (join.JoinType == 2)
                    {
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema12);
                    }

                }
            }
            else
            {
                //Parsing main part of query SQL statement
                foreach (var source in metadata.DataQuerySources)
                {
                    if (source.DataObject == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema13 + metadata.ID);
                    if (string.IsNullOrEmpty(source.Alias))
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema14 + metadata.ID);
                    selectStatement.From.ChildCollection.Add(new SqlTable(GetTableName(source.DataObject), source.Alias));
                }
            }
            #endregion

            #region Build Select Columns
            List<DataQueryColumn> groupCols = metadata.DataQueryColumns.Where(x => x.ColumnType == ColumnType.Group).ToList();
            List<DataQueryColumn> aggregateCols = metadata.DataQueryColumns.Where(x => x.ColumnType == ColumnType.Aggregate).ToList();

            if (groupCols != null && groupCols.Count > 0)
            {
                isCanPaging = false;
                foreach (DataQueryColumn field in groupCols)
                {
                    var dataObject = field.DataObject ?? metadata.DataQuerySources.FirstOrDefault(s => s.ID == field.DataQuerySourceID)?.DataObject;
                    if (dataObject == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, string.Format(CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema15, field.DataObjectID) + metadata.ID);
                    DataColumn dataColumn = field.DataColumn ?? dataObject.Columns.FirstOrDefault(col => col.ID == field.DataObjectColumnID);
                    if (dataColumn == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, string.Format(CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema16, field.DataObjectColumnID) + metadata.ID);

                    var nfield = new SelectListField()
                    {
                        Table = new SqlTable(GetTableName(field.DataObject), metadata.DataQuerySources.First(source => source.ID == field.DataQuerySourceID).Alias),
                        IsUseAlias = true,
                        FieldName = field.DataColumn.ColumnName,
                        FieldAlias = field.Alias,
                        IsUseFieldPrefix = field.ColumnType == ColumnType.Express ? false : true,            //一定要在FieldAlias后面赋值，因为FieldAlias会自动启用前缀。如果是表达式，则禁用前缀，表达式已经提供。
                    };

                    selectStatement.SelectList.ChildCollection.Add(nfield);
                }
                foreach (DataQueryColumn field in aggregateCols)
                {
                    if (field.IsHide == true)
                        continue;
                    var dataObject = field.DataObject ?? metadata.DataQuerySources.FirstOrDefault(s => s.ID == field.DataQuerySourceID)?.DataObject;
                    if (dataObject == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_DQColumnRealtedDOCannotBeNull + metadata.ID);
                    DataColumn dataColumn = field.DataColumn ?? dataObject.Columns.FirstOrDefault(col => col.ID == field.DataObjectColumnID);
                    if (dataColumn == null)
                        throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_DQColumnRealtedDOColumnCannotBeNull + metadata.ID);

                    var nfield = new AggregateField
                    {
                        Table = new SqlTable(GetTableName(field.DataObject), metadata.DataQuerySources.First(source => source.ID == field.DataQuerySourceID).Alias),
                        AggregateType = field.AggregateType,
                        Alias = field.Alias,
                        FieldName = field.DataColumn.ColumnName
                    };

                    selectStatement.AggregateCondition.ChildCollection.Add(nfield);
                }
            }
            else
            {
                isCanPaging = true;
                if (aggregateCols != null && aggregateCols.Count > 0)
                {
                    foreach (DataQueryColumn field in aggregateCols)
                    {
                        if (field.IsHide == true)
                            continue;

                        var dataObject = field.DataObject ?? metadata.DataQuerySources.FirstOrDefault(s => s.ID == field.DataQuerySourceID)?.DataObject;
                        if (dataObject == null)
                            throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_DQColumnRealtedDOCannotBeNull + metadata.ID);
                        var dataColumn = field.DataColumn ?? dataObject.Columns.FirstOrDefault(col => col.ID == field.DataObjectColumnID);
                        if (dataColumn == null)
                            throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_DQColumnRealtedDOColumnCannotBeNull + metadata.ID);

                        var nfield = new AggregateField();
                        nfield.Table = new SqlTable(GetTableName(field.DataObject), metadata.DataQuerySources.First(source => source.ID == field.DataQuerySourceID).Alias);
                        nfield.AggregateType = field.AggregateType;
                        nfield.Alias = field.Alias;
                        nfield.FieldName = field.DataColumn.ColumnName;

                        selectStatement.AggregateCondition.ChildCollection.Add(nfield);
                    }
                }

                List<DataQueryColumn> commonCols = metadata.DataQueryColumns.Where(x => x.ColumnType != ColumnType.Aggregate).ToList();
                if (commonCols != null && commonCols.Count > 0)
                {
                    foreach (DataQueryColumn field in commonCols)
                    {
                        DataObject dataObject = field.DataObject ?? metadata.DataQuerySources.FirstOrDefault(s => s.ID == field.DataQuerySourceID)?.DataObject;

                        #region 大字段处理逻辑
                        //跳过大字段
                        if (field.DataColumn != null)
                        {
                            if (field.DataColumn.IsBigField())
                                continue;
                        }
                        else
                        {
                            if (dataObject != null)
                            {
                                DataColumn dataColumn = field.DataColumn ?? dataObject.Columns.FirstOrDefault(col => col.ID == field.DataObjectColumnID);
                                if (dataColumn != null && dataColumn.IsBigField())
                                {
                                    field.DataColumn = dataColumn;
                                    continue;
                                }
                            }
                        }

                        #endregion

                        bool isHavePermission = field.DataColumn == null || controlContext.IsHasPermission(dataObject, field.DataColumn);

                        SelectListField nfield = BuildSelectListField(field, metadata);
                        if (nfield != null)
                        {
                            //只有有权限，或者开启掩码才查询
                            if (!isHavePermission)
                            {
                                nfield.FieldName = "null";
                                nfield.IsUseFieldPrefix = false;
                                nfield.IsExpress = true;
                            }

                            /***低代码改动:DQ返回结果支持多语言***/
                            if (field.DataColumn != null )
                            {
                                bool isNotReturnRealData = !isHavePermission || !controlContext.IsCanGetRealData(field.DataColumn);
                                if (MaskTool.EnableFASMaskStorage(field.DataColumn.MaskingRule))
                                {
                                    string maskFieldName = DataColumn.GetMaskColumnName(dataObject, field.DataColumn.ColumnName);
                                    //如果没有权限 或者 没有指定获取真实值，则返回掩码值
                                    if (isNotReturnRealData)
                                    {
                                        nfield.FieldName = maskFieldName;
                                        nfield.IsUseFieldPrefix = true;
                                        nfield.IsExpress = false;
                                    }
                                    SelectListField maskField = nfield.Clone() as SelectListField;
                                    maskField.FieldName = maskFieldName;
                                    maskField.FieldAlias = DataColumn.GetMaskColumnName(dataObject, field.Alias);
                                    selectStatement.SelectList.ChildCollection.Add(maskField);
                                }
                                ////如果没启用掩码、启用了列权限，当有权限 但是没有显示指定获取该列的真实值时，返回空
                                //else if (field.DataColumn.IsEnableColumnPermission && isNotReturnRealData)
                                //{
                                //    nfield.FieldName = "null";
                                //    nfield.IsUseFieldPrefix = false;
                                //    nfield.IsExpress = true;
                                //}

                                if (isHavePermission && field.DataColumn.IsInternational)
                                {
                                    //如果当前用户启用了辅助语言列，则返回辅助语言列的数据，暂时使用这种模式
                                    if (true)
                                    {
                                        SelectListField mainI18NField = nfield.Clone() as SelectListField;
                                        Dictionary<string, string> mapping = InternationalTools.GetLanguageMapping(InternationalMode.CurrentUser);
                                        //如果当前语言时辅助语言，则将主语言列的列名替换为对应辅助语言的列名，已达到主语言列例如:Name 返回当前语言列的功能
                                        if (mapping.Count == 1)
                                        {
                                            var realColumnName = InternationalTools.GetRealFieldName(dataObject, field.DataColumn.ColumnName, mapping.First().Key);
                                            nfield.FieldName = realColumnName;
                                        }

                                        Dictionary<string, string> mappingAll = InternationalTools.GetLanguageMapping(controlContext.InternationalMode, controlContext.SpecifiedLanguages);
                                        if (mappingAll.Count > 0)
                                        {
                                            foreach (var langKey in mappingAll.Keys)
                                            {
                                                SelectListField i18NField = nfield.Clone() as SelectListField;
                                                string realTableFieldName = InternationalTools.GetRealFieldName(dataObject, field.DataColumn.ColumnName, langKey);
                                                string logicFieldName = InternationalTools.GetLogicFieldName(field.Alias, mappingAll[langKey]);
                                                i18NField.FieldName = realTableFieldName;
                                                i18NField.FieldAlias = logicFieldName;
                                                selectStatement.SelectList.ChildCollection.Add(i18NField);
                                            }
                                        }

                                        try
                                        {
                                            //将主语言拼接I18N Code返回，例如Main: Name_en_US，以兼容下拉帮助缓存的国际化
                                            var mainLanguage = LanguageService.GetSupportLanguages()?.FirstOrDefault(l => l.IsMainLanguage);
                                            if (mainLanguage != null)
                                            {
                                                string logicFieldName = InternationalTools.GetLogicFieldName(field.Alias, mainLanguage.ID);
                                                mainI18NField.FieldAlias = logicFieldName;
                                                selectStatement.SelectList.ChildCollection.Add(mainI18NField);
                                            }
                                        }
                                        catch (Exception e)
                                        {

                                        }
                                    }
                                }
                            }
                            selectStatement.SelectList.ChildCollection.Add(nfield);
                        }
                    }
                }
            }
            #endregion

            #region Build Filter
            var whereCondition = metadata.DataQueryFilters.Where(x => x.FilterType != FilterType.Aggregate).ToList();

            foreach (var filter in whereCondition)
            {
                ConditionStatement st = new ConditionStatement();
                //将所有Filter放到()中
                string conditionString;
                if (filter.FilterExpression == $"@{DQQueryService.AdvancedFilterString}")
                {
                    if (advanceQuerySQL == null || string.IsNullOrEmpty(advanceQuerySQL.SQL)) continue;
                    else    //高级查询结果存在，则替换@AdvancedFilter，并且增加Parameter处理
                    {
                        isHaveFilter = true;
                        conditionString = $"({advanceQuerySQL.SQL})";
                    }
                }
                else
                {
                    isHaveFilter = true;
                    if (string.IsNullOrEmpty(filter.FilterExpression)) continue;
                    conditionString = $"({filter.FilterExpression})";
                }
                st.ConditionString = conditionString;
                selectStatement.FilterCondition.ChildCollection.Add(st);
            }
            if (!string.IsNullOrEmpty(authSQLs))
            {
                ConditionStatement st = new ConditionStatement();
                //将权限SQLs放到()中
                isHaveFilter = true;
                st.ConditionString = $"({authSQLs})";
                selectStatement.FilterCondition.ChildCollection.Add(st);
            }
            #endregion

            #region Build GroupBy
            foreach (var group in groupCols)
            {
                if (group.DataObject == null)
                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema21 + metadata.ID);
                if (group.DataColumn == null)
                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema22 + metadata.ID);

                GroupByField field = new GroupByField();
                string groupSourceAlias = metadata.DataQuerySources.FirstOrDefault(source => source.ID == group.DataQuerySourceID)?.Alias;
                if (string.IsNullOrEmpty(groupSourceAlias))
                {
                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema14 + metadata.ID);
                }
                field.Table = new SqlTable(GetTableName(group.DataObject), groupSourceAlias);
                field.FieldName = group.DataColumn.ColumnName;
                field.IsUseFieldPrefix = true;
                selectStatement.GroupByCondition.ChildCollection.Add(field);
            }
            #endregion

            #region Build Having
            var havingCondition = metadata.DataQueryFilters.Where(x => x.FilterType == FilterType.Aggregate).ToList();
            foreach (var filter in havingCondition)
            {
                isHaveFilter = true;
                HavingItem st = new HavingItem();
                st.ExpressionString = filter.FilterExpression;
                selectStatement.HavingCondition.ChildCollection.Add(st);
            }
            #endregion

            #region Build OrderBy
            //if (!isSubQuery)    //子查询不需要处理排序
            if (true)    //子查询需要处理排序
            {
                OrderByStatement orderBy = new OrderByStatement();
                //兼容高级查询AdvancedSort处理。高级查询排序条件优先级 > DQ默认排序条件
                if (advancedSorts != null && advancedSorts.Count > 0)
                {
                    foreach (var advancedSort in advancedSorts)
                    {
                        if (!advancedSort.IsUserDefinedColumnSort)
                        {
                            var field = new OrderByField()
                            {
                                Table = new SqlTable(GetTableName(advancedSort.DataQuerySource.DataObject), advancedSort.DataQuerySource.Alias),
                                FieldName = advancedSort.SortColumnName,
                                OrderByType = advancedSort.Sort.SortType == 0 ? OrderByType.Asc : OrderByType.Desc,
                                IsUseFieldPrefix = true
                            };
                            orderBy.ChildCollection.Add(field);
                        }
                        else
                        {
                            var field = new ExprOrderByField()
                            {
                                OrderByType = advancedSort.Sort.SortType == 0 ? OrderByType.Asc : OrderByType.Desc,
                                SortExprContent = advancedSort.UserDefinedSortExpression,
                            };
                            orderBy.ChildCollection.Add(field);
                        }
                    }
                }
                else
                {
                    foreach (var sort in metadata.DataQuerySorts)
                    {
                        //表达式排序
                        if (sort.SortItemType == 1)
                        {
                            var exprSort = new ExprOrderByField()
                            {
                                //排序类型：0:ASC 1:DESC
                                OrderByType = sort.SortType == 1 ? OrderByType.Desc : OrderByType.Asc,
                                SortExprContent = sort.SortExpression
                            };
                            orderBy.ChildCollection.Add(exprSort);
                        }
                        else
                        {
                            //排序列为自定义列
                            if (string.IsNullOrEmpty(sort.DataObjectID) || sort.SortColumnType == ColumnType.Express)
                            {
                                if (sort.DataQueryColumn != null && !string.IsNullOrEmpty(sort.SortExpression))
                                {
                                    var exprSort = new ExprOrderByField()
                                    {
                                        //排序类型：0:ASC 1:DESC
                                        OrderByType = sort.SortType == 1 ? OrderByType.Desc : OrderByType.Asc,
                                        SortExprContent = sort.SortExpression
                                    };
                                    orderBy.ChildCollection.Add(exprSort);
                                }
                            }
                            else
                            {
                                if (sort.DataObject == null)
                                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, string.Format(CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema23, metadata.ID, sort.ID));
                                if (sort.DataColumn == null)
                                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, string.Format(CommonStringClassEngine.QuerySQLBuildStrategy_BuildTableSqlSchema24, metadata.ID, sort.ID));

                                string sortSourceAlias = null;
                                if (string.IsNullOrEmpty(sort.DataQueryColumn?.DataQuerySourceID) == false)
                                {
                                    sortSourceAlias = metadata.DataQuerySources.FirstOrDefault(source => source.ID == sort.DataQueryColumn.DataQuerySourceID).Alias;
                                }
                                else if (string.IsNullOrEmpty(sort.DataQueryColumnID) == false)
                                {
                                    DataQueryColumn dqCol = metadata.DataQueryColumns.FirstOrDefault(col => col.ID == sort.DataQueryColumnID);
                                    if (string.IsNullOrEmpty(dqCol?.DataQuerySourceID) == false)
                                    {
                                        sortSourceAlias = metadata.DataQuerySources.FirstOrDefault(source => source.ID == dqCol.DataQuerySourceID).Alias;
                                    }
                                }
                                if (sortSourceAlias == null)
                                {
                                    sortSourceAlias = metadata.DataQuerySources.First(x => x.DataObjectID == sort.DataObject.ID).Alias;
                                }

                                var field = new OrderByField()
                                {
                                    Table = new SqlTable(GetTableName(sort.DataObject), sortSourceAlias),
                                    FieldName = sort.DataColumn.ColumnName,
                                    OrderByType = sort.SortType == 0 ? SQLDom.OrderByType.Asc : SQLDom.OrderByType.Desc,
                                    IsUseFieldPrefix = true
                                };
                                orderBy.ChildCollection.Add(field);
                            }
                        }
                    }
                }

                selectStatement.StaticOrderByCondition = orderBy;
            }
            #endregion

            return selectStatement;
        }

        /// <summary>
        /// 获取DO表名
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        private static string GetTableName(DataObject data)
        {
            if (data == null)
                return string.Empty;
            if (string.IsNullOrEmpty(data.ActualTableNames))
                return data.LogicTableName;
            else
                return data.ActualTableNames;
        }

        /// <summary>
        /// db str => 枚举
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static DatabaseType DBTypeConvert(string type)
        {
            return DBTypeConverter.Convert(type);
        }

        /// <summary>
        /// 替换自定义变量的结果
        /// </summary>
        /// <param name="oriStr">要替换内容的文本</param>
        /// <param name="varName">形如:@var</param>
        /// <param name="actStr">实际值</param>
        /// <returns></returns>
        public static string Replace(string oriStr, string varName, string actStr)
        {
            /*
             * 正则替换自定义变量，形如:@var
             * 替换场景：@var后面不能跟数字、字母、下划线
             */
            string pattern = $@"{varName}(?![\w])";
            if (string.IsNullOrEmpty(oriStr)) return default;
            if (actStr == null) actStr = "";
            return Regex.Replace(oriStr, pattern, actStr);
        }

        /// <summary>
        /// 构造查询列集合
        /// </summary>
        /// <param name="field"></param>
        /// <param name="metadata"></param>
        /// <param name="i18NCode"></param>
        /// <returns></returns>
        /// <exception cref="FasException"></exception>
        public static SelectListField BuildSelectListField(DataQueryColumn field, DataQuery metadata, string i18NCode = null)
        {
            if (field.IsHide == true)
                return null;

            DataColumn dataColumn = null;
            if (field.ColumnType != ColumnType.Express && !string.IsNullOrEmpty(field.DataQuerySourceID))
            {
                DataObject dataObject = field.DataObject ?? metadata.DataQuerySources.FirstOrDefault(s => s.ID == field.DataQuerySourceID)?.DataObject;
                if (field.ColumnType != ColumnType.Express && dataObject == null)
                    throw new FasException(DataAccessErrorCode.DataQuery.NullValue, CommonStringClassEngine.QuerySQLBuildStrategy_DQColumnRealtedDOCannotBeNull + metadata.ID);
                dataColumn = field.DataColumn ?? dataObject.Columns.FirstOrDefault(col => col.ID == field.DataObjectColumnID);
            }
            SelectListField nfield = new SelectListField()
            {
                IsUseAlias = true,
                IsUseFieldPrefix = true,
                FieldName = dataColumn == null ? "" : dataColumn.ColumnName,
                FieldAlias = field.Alias
            };
            if (field.ColumnType == ColumnType.Express)
            {
                nfield.FieldName = field.ColumnExpression;
                if (string.IsNullOrEmpty(nfield.FieldName))
                    nfield.FieldName = "''";
                nfield.IsUseFieldPrefix = false;
                nfield.IsExpress = true;
            }
            else
            {
                //根据DataQueryColumn.DataQuerySourceID关联取出该数据源的别名
                nfield.Table = new SqlTable(GetTableName(field.DataObject), metadata.DataQuerySources.FirstOrDefault(s => s.ID == field.DataQuerySourceID).Alias);
            }
            return nfield;
        }
    }
}
